package com.srv.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.srv.Config;
import com.srv.common.DataSourceManager;
import com.srv.common.NodeField;
import com.srv.common.TravelField;
import com.srv.model.Node;
import com.srv.model.TheBaseObj;
import com.srv.model.Travel;
import com.srv.model.User;
import com.srv.persistence.INodePersistence;
import com.srv.persistence.PersistenceException;
import com.srv.util.DataBaseUtil;
import com.srv.util.ObjectField;
import com.srv.util.SqlUtil;
import com.srv.util.StringUtil;

public class DBNodePersistence implements INodePersistence {

	private static Log log = LogFactory.getLog(DBNodePersistence.class);
	
	private static final String KEY_TABLE_NAME_PREFIX = "NODE_";
	
	private static final int TABLE_NUM = Config.TABLE_NUM;
	/**
	PID, TRAVEL_ID, NODE_ID, FAVOR_NUM, DOWNLOAD_NUM, REPUB_NUM, IMG_URI, RECORD_URI, 
	CONTEXT, TAG, LONGITUDE, LATITUDE, CREATE_TIME, ISDEL, UPDATE_TIME
	 */
	public Node getNode(long pid, String nodeID) throws PersistenceException {
		Map<ObjectField, Object> keyValues = new HashMap<ObjectField, Object>();
		keyValues.put(NodeField.NODE_ID, nodeID);
		
		List<Node> list = queryNodeList(pid, keyValues);
		if(list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	public Node insertNode(Node node) throws PersistenceException {
		if(node.pid < 1){
			throw new PersistenceException("the column \"pid\" is must NOT NULL when insert node");
		}
		if(node.travelID == null){
			throw new PersistenceException("the column \"travelID\" is must NOT NULL when insert node");
		}
		if(node.nodeID == null){
			throw new PersistenceException("the column \"nodeID\" is must NOT NULL when insert node");
		}
		Date currentTime = new Date();
		if(node.createTime == null){
			node.createTime = currentTime;
		}
		if(node.updateTime == null){
			node.updateTime = currentTime;
		}
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(node.pid).getConnection();
			st = conn.prepareStatement(getInsertSql(node.pid));
			
			st.setLong(1, node.pid);
			st.setString(2, node.travelID);
			st.setString(3, node.nodeID);
			st.setLong(4, node.favorNum);
			st.setLong(5, node.downloadNum);
			st.setLong(6, node.rePubNum);
			st.setString(7, node.img_public_URI);
			st.setString(8, node.record_public_URI);
			st.setString(9, node.getImgInnerURI());
			st.setString(10, node.getRecordInnerURI());
			st.setString(11, node.textContext);
			st.setString(12, node.tag);
			st.setDouble(13, node.longitude);
			st.setDouble(14, node.latitude);
			st.setTimestamp(15, new Timestamp(node.createTime.getTime()));
			st.setInt(16, node.isDel() ? 1 : 0);
			st.setTimestamp(17, new Timestamp(node.updateTime.getTime()));
			
			int flag = st.executeUpdate();
			if(flag <= 0){
				throw new PersistenceException("insert node fail");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		
		return node;
	}

	public List<Node> queryNodeList(long pid, Map<ObjectField, Object> keyValues)
			throws PersistenceException {
		String sql = getSelectSql(pid, keyValues);
		log.debug("The Node query sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<Node> list = new ArrayList<Node>();
		
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			SqlUtil.setStmtParams(st, 1, keyValues);
			rs = st.executeQuery();
			
			while(rs.next()){
				list.add(rsToObject(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return list;
	}
	//TODO
	public void deleteNode(long pid, String nodeID)throws PersistenceException{
		
	}
	public boolean updateNode(long pid, String nodeID, Map<ObjectField, Object> keyValues) throws PersistenceException {
		String sql = getUpdateSql(pid, keyValues);
		log.debug("The Node update sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			int idx = SqlUtil.setStmtParams(st, 1, keyValues);
			st.setLong(idx, pid);
			st.setString(idx + 1, nodeID);
			
			return st.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}
	
	private String getUpdateSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder strBuf = new StringBuilder();
		strBuf.append("UPDATE ");
		strBuf.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			strBuf.append(" SET ").append(SqlUtil.generateSetCause(keyValues));
		}
		strBuf.append(" WHERE PID = ? AND NODE_ID = ?");
		
		return strBuf.toString();
	}
	
	private String getInsertSql(long pid){
		StringBuilder builder = new StringBuilder();
		
		builder.append("INSERT INTO ").append(KEY_TABLE_NAME_PREFIX);
		builder.append(StringUtil.appendZore(pid % TABLE_NUM, 2));
		builder.append("(PID, TRAVEL_ID, NODE_ID, FAVOR_NUM, DOWNLOAD_NUM, REPUB_NUM, IMG_URI, RECORD_URI, IMG_INNER_URI, RECORD_INNER_URI, CONTEXT, TAG, LONGITUDE, LATITUDE, CREATE_TIME, ISDEL, UPDATE_TIME)");
		builder.append(" VALUES ");
		builder.append("(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		
		return builder.toString();
	}
	
	private String getSelectSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ");
		builder.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			builder.append(" WHERE ").append(SqlUtil.generateWhereCause(keyValues));
        }
		
        return builder.toString();
	}
	
	private Node rsToObject(ResultSet rs) throws SQLException{
		Node node = new Node();
		
		node.pid = rs.getLong("PID");
		node.travelID = rs.getString("TRAVEL_ID");
		node.nodeID = rs.getString("NODE_ID");
		node.favorNum = rs.getLong("FAVOR_NUM");
		node.downloadNum = rs.getLong("DOWNLOAD_NUM");
		node.rePubNum = rs.getLong("REPUB_NUM");
		node.img_public_URI = rs.getString("IMG_URI");
		node.record_public_URI = rs.getString("RECORD_URI");
		
		node.img_Inner_URI = Node.stringToList(rs.getString("IMG_INNER_URI"));
		node.record_Inner_URI = Node.stringToList(rs.getString("RECORD_INNER_URI"));
		
		node.textContext = rs.getString("CONTEXT");
		node.tag = rs.getString("TAG");
		node.longitude = rs.getDouble("LONGITUDE");
		node.latitude = rs.getDouble("LATITUDE");
		node.createTime = new Date(rs.getTimestamp("CREATE_TIME").getTime());
		node.setIsDel(rs.getInt("ISDEL"));
		node.updateTime = new Date(rs.getTimestamp("UPDATE_TIME").getTime());
		
		return node;
	}

}
